﻿/*=================================================================
* 版权信息：Copyright (C) 2005-2011 捷点科技 All Rights Reserved.
* 文件名称：CNVP_ProductsDetail.cs
* 创建标识：曾乃强 15158756652 2011-03-17 11:03:01
* 修改标识：
* 功能描述：
===================================================================*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using CNVP.Framework.Helper;
using CNVP.Config;
using CNVP.Framework;

namespace CNVP.JShop.DAL
{
    /// <summary>
    /// 对*CNVP_ProductsDetail*操作数据库(针对SQL Server)的实现
    /// </summary>
    public class CNVP_ProductsDetail
    {
       private string Prefix = DBConfig.Prefix;
        #region DAL
        public int Insert(Model.CNVP_ProductsDetail obj)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("insert into  " + Prefix + "ProductsDetail ([ProductID],[ProDetailNo],[StockNum],[SalePrice],[CostPrice],[MarketPrice],[ProDetailWeight],[Allocation],[IsShow],[StandardValueIDs])");
            sql.Append("values(@ProductID,@ProDetailNo,@StockNum,@SalePrice,@CostPrice,@MarketPrice,@ProDetailWeight,@Allocation,@IsShow,@StandardValueIDs)");
            sql.Append(";SELECT @@IDENTITY");
            SqlParameter[] pra = {
                            new SqlParameter("@ProductID",obj.ProductID),
							new SqlParameter("@ProDetailNo",obj.ProDetailNo),
							new SqlParameter("@StockNum",obj.StockNum),
							new SqlParameter("@SalePrice",obj.SalePrice),
							new SqlParameter("@CostPrice",obj.CostPrice),
							new SqlParameter("@MarketPrice",obj.MarketPrice),
							new SqlParameter("@ProDetailWeight",obj.ProDetailWeight),
							new SqlParameter("@Allocation",obj.Allocation),
							new SqlParameter("@IsShow",obj.IsShow),
							new SqlParameter("@StandardValueIDs",obj.StandardValueIDs)
                                 };
            return Convert.ToInt32(DbHelper.ExecuteScalar(sql.ToString(), pra));
        }

        public int Update(Model.CNVP_ProductsDetail obj)
        {
             StringBuilder sql = new StringBuilder();
             sql.Append("update " + Prefix + "ProductsDetail set [ProductID] = @ProductID,[ProDetailNo] = @ProDetailNo,[StockNum] = @StockNum,[SalePrice] = @SalePrice,[CostPrice] = @CostPrice,[MarketPrice] = @MarketPrice,[ProDetailWeight] = @ProDetailWeight,[Allocation] = @Allocation,[IsShow] = @IsShow,[StandardValueIDs] = @StandardValueIDs where [ID] = @ID");

            SqlParameter[] pra = { 
                            new SqlParameter("@ID",obj.ID),
							new SqlParameter("@ProductID",obj.ProductID),
							new SqlParameter("@ProDetailNo",obj.ProDetailNo),
							new SqlParameter("@StockNum",obj.StockNum),
							new SqlParameter("@SalePrice",obj.SalePrice),
							new SqlParameter("@CostPrice",obj.CostPrice),
							new SqlParameter("@MarketPrice",obj.MarketPrice),
							new SqlParameter("@ProDetailWeight",obj.ProDetailWeight),
							new SqlParameter("@Allocation",obj.Allocation),
							new SqlParameter("@IsShow",obj.IsShow),
							new SqlParameter("@StandardValueIDs",obj.StandardValueIDs)
                                };
             return DbHelper.ExecuteNonQuery(sql.ToString(),pra);
        }

        public int Delete(Model.CNVP_ProductsDetail obj)
        {
           StringBuilder sql = new StringBuilder();
           sql.Append("delete from " + Prefix + "ProductsDetail where [ID] = @ID");
           SqlParameter[] pra = {
                                new SqlParameter("@ID",obj.ID)
						
                                };
           return DbHelper.ExecuteNonQuery(sql.ToString(),pra);
        }

        public List<Model.CNVP_ProductsDetail> LoadAll()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select [ID],[ProductID],[ProDetailNo],[StockNum],[SalePrice],[CostPrice],[MarketPrice],[ProDetailWeight],[Allocation],[IsShow],[StandardValueIDs] from " + Prefix + "ProductsDetail");
            DataTable DT = DbHelper.ExecuteTable(sql.ToString());
            List<Model.CNVP_ProductsDetail> list=new List<Model.CNVP_ProductsDetail>();
            if (DT != null && DT.Rows.Count > 0)
            {
                foreach (DataRow dr in DT.Rows)
                {
                    Model.CNVP_ProductsDetail cnvp_productsdetail = new Model.CNVP_ProductsDetail();
                    cnvp_productsdetail.ID = dr["ID"] is DBNull ? 0 : (int)dr["ID"];
                    cnvp_productsdetail.ProductID = dr["ProductID"] is DBNull ? 0 : (int)dr["ProductID"];
                    cnvp_productsdetail.ProDetailNo = dr["ProDetailNo"] is DBNull ? String.Empty : (string)dr["ProDetailNo"];
                    cnvp_productsdetail.StockNum = dr["StockNum"] is DBNull ? 0 : (int)dr["StockNum"];
                    cnvp_productsdetail.SalePrice = dr["SalePrice"] is DBNull ? (decimal)0.00 : (decimal)dr["SalePrice"];
                    cnvp_productsdetail.CostPrice = dr["CostPrice"] is DBNull ? (decimal)0.00 : (decimal)dr["CostPrice"];
                    cnvp_productsdetail.MarketPrice = dr["MarketPrice"] is DBNull ? (decimal)0.00 : (decimal)dr["MarketPrice"];
                    cnvp_productsdetail.ProDetailWeight = dr["ProDetailWeight"] is DBNull ? (decimal)0.00 : (decimal)dr["ProDetailWeight"];
                    cnvp_productsdetail.Allocation = dr["Allocation"] is DBNull ? String.Empty : (string)dr["Allocation"];
                    cnvp_productsdetail.IsShow = dr["IsShow"] is DBNull ? (byte)0 : (byte)dr["IsShow"];
                    cnvp_productsdetail.StandardValueIDs = dr["StandardValueIDs"] is DBNull ? String.Empty : (string)dr["StandardValueIDs"];
                    list.Add(cnvp_productsdetail);
                }
            }
            return list;

        }

        public Model.CNVP_ProductsDetail LoadById(int ID)
        {
            
            StringBuilder sql = new StringBuilder();
            sql.Append("select [ProductID],[ProDetailNo],[StockNum],[SalePrice],[CostPrice],[MarketPrice],[ProDetailWeight],[Allocation],[IsShow],[StandardValueIDs] from " + Prefix + "ProductsDetail");
            sql.Append(" where [ID] = @ID");
            SqlParameter[] pra = {
                                 new SqlParameter("@ID",ID)
                                    };
            DataTable DT = DbHelper.ExecuteTable(sql.ToString(),pra);
            Model.CNVP_ProductsDetail cnvp_productsdetail = new Model.CNVP_ProductsDetail();
            if (DT != null && DT.Rows.Count > 0)
            {
                
                DataRow dr = DT.Rows[0];
                cnvp_productsdetail.ProductID = dr["ProductID"] is DBNull ? 0 : (int)dr["ProductID"];
                cnvp_productsdetail.ProDetailNo = dr["ProDetailNo"] is DBNull ? String.Empty : (string)dr["ProDetailNo"];
                cnvp_productsdetail.StockNum = dr["StockNum"] is DBNull ? 0 : (int)dr["StockNum"];
                cnvp_productsdetail.SalePrice = dr["SalePrice"] is DBNull ? (decimal)0.00 : (decimal)dr["SalePrice"];
                cnvp_productsdetail.CostPrice = dr["CostPrice"] is DBNull ? (decimal)0.00 : (decimal)dr["CostPrice"];
                cnvp_productsdetail.MarketPrice = dr["MarketPrice"] is DBNull ? (decimal)0.00 : (decimal)dr["MarketPrice"];
                cnvp_productsdetail.ProDetailWeight = dr["ProDetailWeight"] is DBNull ? (decimal)0.00 : (decimal)dr["ProDetailWeight"];
                cnvp_productsdetail.Allocation = dr["Allocation"] is DBNull ? String.Empty : (string)dr["Allocation"];
                cnvp_productsdetail.IsShow = dr["IsShow"] is DBNull ? (byte)0 : (byte)dr["IsShow"];
                cnvp_productsdetail.StandardValueIDs = dr["StandardValueIDs"] is DBNull ? String.Empty : (string)dr["StandardValueIDs"];
            } 
            return cnvp_productsdetail;
        }
        public Model.CNVP_ProductsDetail GetSingle_CNVP_ProductsDetailByPIDAndSID(int pID, string sID)
        {
            
            StringBuilder sql = new StringBuilder();
            sql.Append("select top 1 [ID],[ProductID],[ProDetailNo],[StockNum],[SalePrice],[CostPrice],[MarketPrice],[ProDetailWeight],[Allocation],[IsShow],[StandardValueIDs] from " + Prefix + "ProductsDetail");
            sql.Append(" where [ProductID] = @pID and [StandardValueIDs]=@sID");
            SqlParameter[] pra = {
                                 new SqlParameter("@pID",pID),new SqlParameter("@sID",sID)
						
                                    };
            DataTable DT = DbHelper.ExecuteTable(sql.ToString(),pra);
            Model.CNVP_ProductsDetail cnvp_productsdetail = new Model.CNVP_ProductsDetail();
            if (DT != null && DT.Rows.Count > 0)
            {
                DataRow dr = DT.Rows[0];
                cnvp_productsdetail.ID = dr["ID"] is DBNull ? 0 : (int)dr["ID"];
                cnvp_productsdetail.ProductID = dr["ProductID"] is DBNull ? 0 : (int)dr["ProductID"];
                cnvp_productsdetail.ProDetailNo = dr["ProDetailNo"] is DBNull ? String.Empty : (string)dr["ProDetailNo"];
                cnvp_productsdetail.StockNum = dr["StockNum"] is DBNull ? 0 : (int)dr["StockNum"];
                cnvp_productsdetail.SalePrice = dr["SalePrice"] is DBNull ? (decimal)0.00 : (decimal)dr["SalePrice"];
                cnvp_productsdetail.CostPrice = dr["CostPrice"] is DBNull ? (decimal)0.00 : (decimal)dr["CostPrice"];
                cnvp_productsdetail.MarketPrice = dr["MarketPrice"] is DBNull ? (decimal)0.00 : (decimal)dr["MarketPrice"];
                cnvp_productsdetail.ProDetailWeight = dr["ProDetailWeight"] is DBNull ? (decimal)0.00 : (decimal)dr["ProDetailWeight"];
                cnvp_productsdetail.Allocation = dr["Allocation"] is DBNull ? String.Empty : (string)dr["Allocation"];
                cnvp_productsdetail.IsShow = dr["IsShow"] is DBNull ? (byte)0 : (byte)dr["IsShow"];
                cnvp_productsdetail.StandardValueIDs = dr["StandardValueIDs"] is DBNull ? String.Empty : (string)dr["StandardValueIDs"];
            }
            return cnvp_productsdetail;
        }
        #endregion

        public List<Model.CNVP_ProductsDetail> LoadAllByProductID(int ProductID)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select [ID],[ProDetailNo],[StockNum],[SalePrice],[CostPrice],[MarketPrice],[ProDetailWeight],[Allocation],[IsShow],[StandardValueIDs] from " + Prefix + "ProductsDetail");
            sql.Append(" where [ProductID] = @ProductID");
            SqlParameter[] pra = {
                                 new SqlParameter("@ProductID",ProductID)
						
                                    };
            DataTable DT = DbHelper.ExecuteTable(sql.ToString(),pra); 
            List<Model.CNVP_ProductsDetail> list = new List<Model.CNVP_ProductsDetail>();
            if (DT != null && DT.Rows.Count > 0)
            {
                foreach (DataRow dr in DT.Rows)
                {
                    Model.CNVP_ProductsDetail cnvp_productsdetail = new Model.CNVP_ProductsDetail();
                    cnvp_productsdetail.ID = dr["ID"] is DBNull ? 0 : (int)dr["ID"];
                    cnvp_productsdetail.ProDetailNo = dr["ProDetailNo"] is DBNull ? String.Empty : (string)dr["ProDetailNo"];
                    cnvp_productsdetail.StockNum = dr["StockNum"] is DBNull ? 0 : (int)dr["StockNum"];
                    cnvp_productsdetail.SalePrice = dr["SalePrice"] is DBNull ? (decimal)0.00 : (decimal)dr["SalePrice"];
                    cnvp_productsdetail.CostPrice = dr["CostPrice"] is DBNull ? (decimal)0.00 : (decimal)dr["CostPrice"];
                    cnvp_productsdetail.MarketPrice = dr["MarketPrice"] is DBNull ? (decimal)0.00 : (decimal)dr["MarketPrice"];
                    cnvp_productsdetail.ProDetailWeight = dr["ProDetailWeight"] is DBNull ? (decimal)0.00 : (decimal)dr["ProDetailWeight"];
                    cnvp_productsdetail.Allocation = dr["Allocation"] is DBNull ? String.Empty : (string)dr["Allocation"];
                    cnvp_productsdetail.IsShow = dr["IsShow"] is DBNull ? (byte)0 : (byte)dr["IsShow"];
                    cnvp_productsdetail.StandardValueIDs = dr["StandardValueIDs"] is DBNull ? String.Empty : (string)dr["StandardValueIDs"];
                    list.Add(cnvp_productsdetail);
                }
            } 
            return list;

        }

        public int DeleteNotIn(int ProductID,string nowid)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("delete from " + Prefix + "ProductsDetail where [ProductID]=" + ProductID + " and [ID] not in (" + nowid + ")");
            return DbHelper.ExecuteNonQuery(sql.ToString());
        }
        /// <summary>
        /// 配送更新库存 -库存
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int UpdateNum(int ProductsDetailID, int OrderNum,int type)
        {
            StringBuilder sql = new StringBuilder();
            if (type == 0)
            {
                sql.Append("update " + Prefix + "ProductsDetail set [StockNum] = StockNum - @OrderNum where [ID] = @ID");
            }
            else
            {
                sql.Append("update " + Prefix + "ProductsDetail set [StockNum] = StockNum + @OrderNum where [ID] = @ID");
            }
            SqlParameter[] pra = { 
                            new SqlParameter("@ID",ProductsDetailID),
							new SqlParameter("@OrderNum",OrderNum)
                                };
            return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }
    }
}

